package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import bean.Teacher;
import ojdbc.ConnORCL;

public class TDao {
	public Teacher GetSco(String tea_id , String year)throws Exception
	{
		//System.out.println(tea_id);
		Teacher tea = new Teacher();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int x = 0;
		
		String sql = "select t.tea_name ,s.stu_name , ch.grade ,c.c_id,c.c_name ,cl.cla_id, cl.cla_name from teacher t join classtable ct on t.tea_id = ct.tea_id join student s on s.cla_id = ct.cla_id join course c on c.c_id = ct.c_id join chooses ch on s.stu_id = ch.stu_id and ct.c_id = ch.c_id join class cl on ct.cla_id = cl.cla_id   where t.tea_id = ? and ct.ct_year = ?";
		String sql2 = "select distinct ct_year from teacher t join classtable ct on t.tea_id = ct.tea_id where t.tea_id = ?";

		try
		{
			conn = ConnORCL.getConnection();
			ps = conn.prepareStatement(sql2);
			ps.setString(1, tea_id);
			rs = ps.executeQuery();
			while(rs.next())
			{
				//System.out.println(rs.getString(1));
				/*System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
				System.out.println(rs.getString(4));
				System.out.println(rs.getString(5));
				System.out.println(rs.getString(6));
				System.out.println(rs.getString(7));
				System.out.println(rs.getInt(8));*/
				
				tea.getYear().add(rs.getString(1));
				if(rs.getString(1) == year)
				{
					x = 1;
				}
			}
			ConnORCL.ReleaseResource( null, ps, rs);
			if(x == 1)
			{
				ps = conn.prepareStatement(sql);
				ps.setString(1, tea_id);
				ps.setString(2, year);
				rs = ps.executeQuery();
				while(rs.next())
				{
					/*System.out.println(rs.getString(1));
					System.out.println(rs.getString(2));
					System.out.println(rs.getDouble(3));
					System.out.println(rs.getString(4));
					System.out.println(rs.getString(5));
					System.out.println(rs.getString(6));
					System.out.println(rs.getString(7));*/
					tea.setTea_name(rs.getString(1));
					/*System.out.println("1111"+tea.getTea_name());*/
					tea.setStudent(rs.getString(2),rs.getDouble(3));
					tea.setCourse(rs.getString(4), rs.getString(5));
					tea.setClass(rs.getString(6), rs.getString(7));
				}
			}
			else
			{
				ps = conn.prepareStatement(sql);
				ps.setString(1, tea_id);
				ps.setString(2, tea.getYear().get(0));
				rs = ps.executeQuery();
				while(rs.next())
				{
					tea.setTea_name(rs.getString(1));
					tea.setStudent(rs.getString(2),rs.getDouble(3));
					tea.setCourse(rs.getString(4), rs.getString(5));
					tea.setClass(rs.getString(6), rs.getString(7));
				}
			}
			
			
		}
		finally
		{
			/*System.out.println(tea.getTea_name());
			System.out.println(tea.getYear());*/
			ConnORCL.ReleaseResource( conn, ps, rs);
		}
		return tea;
	}
	public Teacher ChangeData(String tea_id,String cla_id , String c_id , String year)throws Exception
	{
		Teacher tea = new Teacher();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql1 = "select s.stu_name , ch.grade from teacher t join classtable ct on t.tea_id = ct.tea_id join student s on s.cla_id = ct.cla_id join course c on c.c_id = ct.c_id join chooses ch on s.stu_id = ch.stu_id and ct.c_id = ch.c_id join class cl on ct.cla_id = cl.cla_id  where t.tea_id = ? and cla_id = ? and ct_year = ?";
		String sql2 = "select s.stu_name , ch.grade from teacher t join classtable ct on t.tea_id = ct.tea_id join student s on s.cla_id = ct.cla_id join course c on c.c_id = ct.c_id join chooses ch on s.stu_id = ch.stu_id and ct.c_id = ch.c_id join class cl on ct.cla_id = cl.cla_id  where t.tea_id = ? and c_id = ? and ct_year = ?";
		String sql3 = "select s.stu_name , ch.grade from teacher t join classtable ct on t.tea_id = ct.tea_id join student s on s.cla_id = ct.cla_id join course c on c.c_id = ct.c_id join chooses ch on s.stu_id = ch.stu_id and ct.c_id = ch.c_id join class cl on ct.cla_id = cl.cla_id  where t.tea_id = ? and cla_id = ? and c_id = ? and ct_year = ?";
		String sql4 = "select s.stu_name , ch.grade from teacher t join classtable ct on t.tea_id = ct.tea_id join student s on s.cla_id = ct.cla_id join course c on c.c_id = ct.c_id join chooses ch on s.stu_id = ch.stu_id and ct.c_id = ch.c_id join class cl on ct.cla_id = cl.cla_id  where t.tea_id = ? and ct_year = ?";
		
		try
		{
			conn = ConnORCL.getConnection();
			if(c_id.equals("0")&&cla_id.equals("0")){
				ps = conn.prepareStatement(sql4);
				ps.setString(1, tea_id);
				ps.setString(2, year);
				rs = ps.executeQuery();
				while(rs.next())
				{
					System.out.println(rs.getString(1));
					System.out.println(rs.getString(2));
					/*System.out.println(rs.getDouble(3));
					System.out.println(rs.getDouble(4));
					System.out.println(rs.getString(5));*/
					tea.setStudent(rs.getString(1),rs.getDouble(2));
				}
			}
			else if(cla_id.equals("0")){
				ps = conn.prepareStatement(sql2);
				ps.setString(1, tea_id);
				ps.setString(2, c_id);
				ps.setString(3, year);
				rs = ps.executeQuery();
				while(rs.next())
				{
					System.out.println(rs.getString(1));
					System.out.println(rs.getString(2));
					/*System.out.println(rs.getDouble(3));
					System.out.println(rs.getDouble(4));
					System.out.println(rs.getString(5));*/
					tea.setStudent(rs.getString(1),rs.getDouble(2));
				}
			}
			else if(c_id.equals("0")){
				ps = conn.prepareStatement(sql1);
				ps.setString(1, tea_id);
				ps.setString(2, cla_id);
				ps.setString(3, year);
				rs = ps.executeQuery();
				while(rs.next())
				{
					System.out.println(rs.getString(1));
					System.out.println(rs.getString(2));
					/*System.out.println(rs.getDouble(3));
					System.out.println(rs.getDouble(4));
					System.out.println(rs.getString(5));*/
					tea.setStudent(rs.getString(1),rs.getDouble(2));
				}
			}
			else {
				ps = conn.prepareStatement(sql3);
				ps.setString(1, tea_id);
				ps.setString(2, cla_id);
				ps.setString(3, c_id);
				ps.setString(4, year);
				rs = ps.executeQuery();
				while(rs.next())
				{
					System.out.println(rs.getString(1));
					System.out.println(rs.getString(2));
					/*System.out.println(rs.getDouble(3));
					System.out.println(rs.getDouble(4));
					System.out.println(rs.getString(5));*/
					tea.setStudent(rs.getString(1),rs.getDouble(2));
				}
			}
			
		}
		finally
		{
			ConnORCL.ReleaseResource( conn, ps, null);
		}
		return tea;
	}
	
	public Teacher TeaClassTable(String tea_id , String year)throws Exception
	{
		//System.out.println(tea_id);
		Teacher tea = new Teacher();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = " select c_name , c_time , c_point , ct.g_id ,tea_name , cla_name  from  classtable ct join course c on c.c_id = ct.c_id  join teacher t on t.tea_id = ct.tea_id   join class cla on cla.cla_id = ct.cla_id where t.tea_id = ? and ct_year = ?";
		String sql2 = "select distinct ct_year from teacher t join classtable ct on t.tea_id = ct.tea_id where t.tea_id = ?";
		try
		{
			conn = ConnORCL.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, tea_id);
			ps.setString(2, year);
			rs = ps.executeQuery();
			while(rs.next())
			{
				/*System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
				System.out.println(rs.getDouble(4));
				System.out.println(rs.getString(5));*/
				tea.setClaTab(rs.getString(1), rs.getString(2), rs.getDouble(3), rs.getInt(4));
				tea.setTea_name(rs.getString(5));
				tea.setClas(rs.getString(6));
				
			}
			ConnORCL.ReleaseResource( null, ps, rs);
			ps = conn.prepareStatement(sql2);
			ps.setString(1, tea_id);
			rs = ps.executeQuery();
			while(rs.next())
			{
				tea.getYear().add(rs.getString(1));
			}
		}
		finally
		{
			ConnORCL.ReleaseResource( conn, ps, rs);
		}
		return tea;
	}
	public Teacher TeaClaTab(String tea_id , String year)throws Exception
	{
		Teacher tea = new Teacher();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = " select c_name , c_time , c_point , ct.g_id , cla_name  from  classtable ct join course c on c.c_id = ct.c_id  join teacher t on t.tea_id = ct.tea_id   join class cla on cla.cla_id = ct.cla_id where t.tea_id = ? and ct_year = ?";
		try
		{
			conn = ConnORCL.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, tea_id);
			ps.setString(2, year);
			rs = ps.executeQuery();
			while(rs.next())
			{
				/*System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
				System.out.println(rs.getDouble(4));
				System.out.println(rs.getString(5));*/
				tea.setClaTab(rs.getString(1), rs.getString(2), rs.getDouble(3), rs.getInt(4));
				tea.setClas(rs.getString(5));
			}
		}
		finally
		{
			ConnORCL.ReleaseResource( conn, ps, rs);
		}
		return tea;
	}
}

